from flask import Flask, make_response, jsonify, g, current_app as app,Blueprint,request
from sqlalchemy import select,insert,update,func,join
from sqlalchemy.sql import text
from eim.tools.searchtool import SearchCondition,SearchConditionList,MatchLogic,PartnerRS,GlobalSearch
from eim.tools.dbtool import DBTool
from eim import db
from eim.result.simple_result import SimpleResult


uffieldbp=Blueprint("userformfieldbp","userformfieldbp",__name__,url_prefix='/uffields')

def getallfields(fcode:str,version:str)->list:
    """
    获取所有自定义表单的列
    
    Keyword arguments:
    argument 表单编号
    Return: 列列表
    """
    try:
        from eim.tools.searchtool import GlobalSearch
        gs = GlobalSearch(db)
        sl = SearchConditionList()
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("formcode",MatchLogic.from_Name("EQ"),fcode)
        sl.appendCondition(rs,so)
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("ver",MatchLogic.from_Name("EQ"),version)
        sl.appendCondition(rs,so)
        orderfield=['issysfield']
        ls = gs.getTableData("formfields",None,sl,orderfds=orderfield,odtype="asc")
        return ls
    except Exception as es:
        raise es
    


@uffieldbp.route("/getfields/<fcode>/<version>",methods=["GET"])
def getufFields(fcode:str,version:str):
    """
    获取指定窗体编号的窗体列

    Args:
        fcode (str): 窗体编号
        version (str): 字段版本
    """
    try:
        res = getallfields(fcode,version)
        ls:list=[]
        for record in res:
            if record["issysfield"]=="00":
                ls.append(record)
            else:
                pass    
        return SimpleResult(status='T',value=ls).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/getAllfields/<fcode>/<version>",methods=["GET"])
def getufAllFields(fcode:str,version:str):
    """
    获取指定表单指定版本的所有字段,包含系统字段

    Args:
        fcode (str): 自定义表单编号
        version (str): 版本号
    """
    try:
        res = getallfields(fcode,version)
        return SimpleResult(status='T',value=res).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()

@uffieldbp.route("/saveFields/<fcode>/<version>",methods=["POST"])
def saveFields(fcode:str,version:str):
    """
    保存指定窗体的列

    Args:
        fcode (str): 窗体编号
        version (str): 字段版本
    """
    def addFields(fields:list):
        # 新增字段
        try:
            if len(fields)<=0:
                return
            dbtool = DBTool(db)
            for v in fields:
                fieldcode = dbtool.getNewCode("formfields",'ff','',16)
                v["fieldcode"]=fieldcode
            gs = GlobalSearch(db)
            gs.appentData("formfields",fields)
        except Exception as es:
            raise es
    
    def upgradeFields(fields:list):
        # 更新自定义表单列数据
        try:
            if len(fields)<=0:
                return
            for v in fields:
                sl = SearchConditionList()
                rs = PartnerRS.from_name("AND")
                so = SearchCondition("fieldcode",MatchLogic.from_Name('EQ'),v["fieldcode"])
                sl.appendCondition(rs,so)
                rs = PartnerRS.from_name("AND")
                so = SearchCondition("ver",MatchLogic.from_Name('EQ'),v["ver"])
                sl.appendCondition(rs,so)
                gs = GlobalSearch(db)
                gs.upgradeData("formfields",v,sl)
        except Exception as es:
            raise es
    
    def delFields(fields:list):
        try:
            if len(fields)<=0:
                return
            for v in fields:
                
                sl = SearchConditionList()
                rs = PartnerRS.from_name("AND")
                so = SearchCondition("fieldcode",MatchLogic.from_Name('EQ'),v["fieldcode"])
                sl.appendCondition(rs,so)
                rs = PartnerRS.from_name("AND")
                so = SearchCondition("ver",MatchLogic.from_Name('EQ'),v["ver"])
                sl.appendCondition(rs,so)
                gs = GlobalSearch(db)
                gs.deleteData("formfields",sl)
        except Exception as es:
            raise es
    
    try:
        pms = request.json
        fields = pms["fields"]
        current_fs = getallfields(fcode,version)
        
        #清理掉系统标记列
        curfields = []
        for i in range(0,len(current_fs)):
            if(current_fs[i]["issysfield"]=='00'):
                curfields.append(current_fs[i])
        
        # 需要更新的字段列表
        upfs = []
        # 需要新增的字段列表
        newfs = []
        # 需要删除的字段列表
        delfs = []
        
        for nv in fields:
            if "ID" in nv.keys():
                del nv["ID"]
            # 待删除的索引
            dindex=-1
            j=0
            for ov in curfields:
                if nv["fieldcode"]==ov["fieldcode"]:
                    upfs.append(nv)
                    dindex=j
                j=j+1
            if dindex==-1:
                # 新的列表中的项在现有列表中不存在时视为新的项
                newfs.append(nv)
            else:
                # 如果在新的列表中发现了循环项，需删除现有项
                del curfields[dindex]
        delfs = curfields
        addFields(newfs)
        upgradeFields(upfs)
        delFields(delfs)
        return SimpleResult(status='T',value="").toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    

@uffieldbp.route("/getfieldlookup/<fcode>/<version>",methods=["GET"])    
def getfieldlookup(fcode:str,version:str):
    """
    获取指定表单的列列表,只包含fieldcode和cname

    Args:
        fcode (str): 表单编码
    """
    try:

        result:list = []
        
        sl = SearchConditionList()
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("formcode",MatchLogic.from_Name('EQ'),fcode)        
        sl.appendCondition(rs,so)
        
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("ver",MatchLogic.from_Name('EQ'),version)        
        sl.appendCondition(rs,so)
        gs = GlobalSearch(db)
        
        fl = ["fieldcode","cname","ename"]
        res = gs.getTableData("formfields",fl,sl)      
        return SimpleResult(status="T",value=res).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()

@uffieldbp.route("/getFieldVersionList/<fcode>",methods=['GET'])
def getFieldVersionList(fcode:str):
    """
    获取指定自定义表单的字段版本列表

    Args:
        fcode (str): 自定义表单的编号
    """
    try:
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("formcode",MatchLogic.from_Name('EQ'),fcode)
        sl = SearchConditionList()
        sl.appendCondition(rs,so)
        gs = GlobalSearch(db)
        data = gs.getTableData('formfields',None,sl)
        return SimpleResult(status='T',value=data).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()

#-----------------版本相关api--------------------------------------
    
@uffieldbp.route("/getNewFieldVercode/<fcode>",methods=['GET'])
def getNewFieldVercode(fcode:str):
    """
    获取新的自定义表单版本编码

    Args:
        fcode (str): 自定义表单的表单编码
    """
    try:
        dt = DBTool(db)
        tname = f"userformversion_{fcode}"
        newcode = dt.getNewCode(tname,'V','',4)
        return SimpleResult(status='T',value=newcode).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/returnNewFieldVercode",methods=['POST'])
def returnNewFieldVercode():
    """
    退换未用的版本号
    """
    try:
        params = request.json
        dt = DBTool(db)
        fcode = params["fcode"]
        vcode = params["code"]
        tname = f"userformversion_{fcode}"
        rc=dt.returnCode(tname,'',vcode)
        return SimpleResult(status='T',value=rc).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/appendUFV",methods=['PUT'])   
def appendUFV():
    """
    新增自定义窗体的版本
    """
    def insertUnitUserField(fcode:str,ver:str,parentCols:list,gs:GlobalSearch):
        try:
            val = {
                "fieldcode":"",
                "ename":"makeuser",
                "cname":"制单人",
                "fieldtype":"varchar",
                "fieldlen":"100",
                "precision":"0",
                "isnull":"00",
                "islist":"00",
                "listvaluefield":"",
                "listsouecetcode":"",
                "formcode":fcode,
                "defvalue":"",
                "ver":ver,
                "issysfield":"01"
            }
            val3 = {
                "fieldcode":"",
                "ename":"mktime",
                "cname":"创建时间",
                "fieldtype":"varchar(30)",
                "fieldlen":"300",
                "precision":"0",
                "isnull":"00",
                "islist":"00",
                "listvaluefield":"",
                "listsouecetcode":"",
                "formcode":fcode,
                "defvalue":"",
                "ver":ver,
                "issysfield":"01"
            }
            val4 = {
                "fieldcode":"",
                "ename":"jztime",
                "cname":"记账时间",
                "fieldtype":"varchar(30)",
                "fieldlen":"300",
                "precision":"0",
                "isnull":"00",
                "islist":"00",
                "listvaluefield":"",
                "listsouecetcode":"",
                "formcode":fcode,
                "defvalue":"",
                "ver":ver,
                "issysfield":"01"
            }
            
            val1={
               "fieldcode":"",
                "ename":"jzflag",
                "cname":"记账标记",
                "fieldtype":"varchar",
                "fieldlen":"10",
                "precision":"0",
                "isnull":"00",
                "islist":"00",
                "listvaluefield":"",
                "listsouecetcode":"",
                "formcode":fcode,
                "defvalue":"00",
                "ver":ver,
                "issysfield":"01" 
            }
            
            val2={
               "fieldcode":"",
                "ename":"jzuser",
                "cname":"记账人",
                "fieldtype":"varchar",
                "fieldlen":"100",
                "precision":"0",
                "isnull":"00",
                "islist":"00",
                "listvaluefield":"",
                "listsouecetcode":"",
                "formcode":fcode,
                "defvalue":"",
                "ver":ver,
                "issysfield":"01" 
            }
            
            vl = list()
            dt = DBTool(db)
            fieldcode = dt.getNewCode("formfields",'ff','',16)
            val["fieldcode"]=fieldcode
            
            fieldcode = dt.getNewCode("formfields",'ff','',16)
            val3["fieldcode"]=fieldcode
            
            fieldcode = dt.getNewCode("formfields",'ff','',16)
            val1["fieldcode"]=fieldcode
            
            fieldcode = dt.getNewCode("formfields",'ff','',16)
            val4["fieldcode"]=fieldcode
            
            fieldcode = dt.getNewCode("formfields",'ff','',16)
            val2["fieldcode"]=fieldcode
            
            pvl = list()
            for fd  in parentCols:
                ffcode = dt.getNewCode("formfields",'ff','',16)
                fd["fieldcode"] = ffcode
                pvl.append(fd)
            gs.appentData("formfields",pvl)
            
            vl.append(val)
            vl.append(val3)
            vl.append(val1)
            vl.append(val4)
            vl.append(val2)
            gs.appentData("formfields",vl)
        except Exception as es:
            print(vl)
            print(str(es))
            raise es
        
    try:
        gs = GlobalSearch(db)
        # 写入新的版本和写入制表人字段是事务执行
        gs.beginTrans()
        
        params = request.json
        fcode = params["ufcode"]
        vercode = params["vercode"]
        parentfields = params["parentFields"]
        del params["parentFields"]
        rc = gs.appentData("userformversion",params)
        insertUnitUserField(fcode,vercode,parentfields,gs)
        # 提交事务
        gs.commit()
        return SimpleResult(status='T',value=rc).toDict()
    except Exception as es:
        gs.rollback()
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()

@uffieldbp.route('/getVersionList/<fcode>',methods=['GET'])    
def getVersionList(fcode:str):
    """
    通过表单获取版本列表

    Args:
        fcode (str): 表单编码
    """
    try:
        dt = DBTool(db)
        gs = GlobalSearch(db)
        rs = PartnerRS.from_name('AND')
        so = SearchCondition('ufcode',MatchLogic.from_Name('EQ'),fcode)
        ls = SearchConditionList()
        ls.appendCondition(rs,so)
        vlist = gs.getTableData('userformversion',None,ls)
        return SimpleResult(status='T',value=vlist).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
    
@uffieldbp.route('/finalizeUserForm',methods=['POST'])
def finalizeUserForm():
    """
    完结自定义表单的调整
    """
    try:
        
        param = request.json
        fcode = param["fcode"]
        version = param["version"]
        dtname = "%s_%s"%(fcode,version)
        fds = getallfields(fcode,version)
        coltextlist = []
        coltextlist.append(" `ID` int(32) NOT NULL AUTO_INCREMENT  COMMENT 'ID;自增长主键'")
        for col in fds:
            coltxt = " `%s` %s %s %s COMMENT '%s'"
            ft=''
            if col["fieldtype"]=="int" or col["fieldtype"]=="bigint" or col["fieldtype"]=="varchar":
                fl = col["fieldlen"]
                ft=f"varchar({fl})"
            elif col["fieldtype"]=="number":
                fl=col["fieldlen"]
                pre = col["precision"]
                ft = f"DECIMAL({fl},{pre})"
            elif col["fieldtype"]=="datetime":
                ft = "datetime"
            else:
                ft = "varchar(100)"
            isnull =""
            if col["isnull"]=="01":
                isnull="NOT NULL"
            
            comment=col["cname"]
            
            defaultval = col["defvalue"] if col["defvalue"]=="" else "default '"+ col["defvalue"]+"'"
            
            
            fname = col["ename"]
            coltxt = coltxt%(fname,ft,isnull,defaultval,comment)
            coltextlist.append(coltxt)
        str_col = ""
        for col  in coltextlist:
            str_col=str_col+col+","
        sqltxt=f"CREATE TABLE {dtname}({str_col} PRIMARY KEY (ID))"
        
        gs = GlobalSearch(db)
        params = {
            "finalize":"01"
        }
        
        ls = SearchConditionList()
        rs = PartnerRS.from_name('AND')
        so = SearchCondition('ufcode',MatchLogic.from_Name('EQ'),fcode)
        ls.appendCondition(rs,so)
        rs = PartnerRS.from_name('AND')
        so = SearchCondition('vercode',MatchLogic.from_Name('EQ'),version)
        ls.appendCondition(rs,so)
        # 开启事务
        gs.beginTrans()
        gs.executeText(sqltxt)
        gs.upgradeData("userformversion",params,ls)
        # 提交事务
        gs.commit()
        
        return SimpleResult(status='T',value="结案成功").toDict() 
    except Exception as es:
        # 回滚事务
        gs.rollback()
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()


@uffieldbp.route('/getTaskListByRoleCode/<roleCode>',methods=['GET'])
def getTaskListByRoleCode(roleCode:str):
    """
    根据角色列表获取拥有权限填写的任务单列表

    Args:
        roleCode (str): 角色编号
    """
    try:
        dt = GlobalSearch(db)
        sql:str = "select u.* from rs_roleoperate rr left join operate  o on rr.ocode =o.ocode\
            left join userform u on o.formcode =u.fcode\
                left join userformversion u2 on u2.ufcode = u.fcode and u2.vercode =u.activeversion\
                    where rr.rcode = '%s' and o.powercode ='PW000018' and u.status = '01' and u2.finalize = '01'"%(roleCode)
        res = dt.executeText(sql)
        rows:list=[]
        for item in res.fetchall():
            rows.append(item._asdict())
        return SimpleResult(status='T',value=rows).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()

@uffieldbp.route("/upVerPermission",methods=["POST"])    
def upgradeVerPermission():
    """
    更新版本权限配置
    """
    try:
        reqparam = request.json
        
        dt = GlobalSearch(db)
        ls = SearchConditionList()
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("ID",MatchLogic.from_Name("EQ"),reqparam["ID"])
        ls.appendCondition(rs,so)
        vals={
            "qryflag":reqparam["qryflag"],
            "delflag":reqparam["delflag"],
            "accountflag":reqparam["accountflag"],
            "edtflag":reqparam["edtflag"]
        }
        rc=dt.upgradeData("userformversion",vals,ls)
        return SimpleResult(status='T',value=rc).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    

@uffieldbp.route("/getVersionPermission/<fcode>/<version>",methods=["GET"])
def getVersionPermission(fcode:str,version:str):
    """
    获取指定表单及指定版本的数据权限

    Args:
        fcode (str): 表单编号
        version (str): 表单版本
    """
    try:
        gs = GlobalSearch(db)
        
        sl = SearchConditionList()
        
        rs = PartnerRS.from_name("AND")
        so = SearchCondition("ufcode",MatchLogic.from_Name("EQ"),fcode)
        sl.appendCondition(rs,so)
        so = SearchCondition("vercode",MatchLogic.from_Name("EQ"),version)
        sl.appendCondition(rs,so)
        res = gs.getTableData("userformversion",pmlst=sl)
        return SimpleResult(status="T",value=res[0]).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
    
@uffieldbp.route("/getTaskRecord/<tname>/<int:ps>/<int:pn>",methods=["POST"])
def getTaskRecord(tname:str,ps:int,pn:int):
    """
    获取指定自定义任务的记录
    Args:
        tname (str): 表名
    """ 
    try:
        params = request.json
        gs = GlobalSearch(db)
        sl = SearchConditionList()
        for pm in params:
            rs =PartnerRS.from_name(pm["RS"])
            cd = pm["CD"]
            so = SearchCondition(cd["field"],MatchLogic.from_Name(cd["logic"]),cd["value"])
            sl.appendCondition(rs,so)
        res = gs.pageTableInfo(pn,ps,tname,[],sl)
        return SimpleResult(status='T',value=res).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/getFormVerLookup/<fcode>",methods=["GET"])
def getFormVerLookup(fcode:str):
    """
    获取指定自定义表单编号的版本引用数据
    Args:
        fcode (str): 表单编号
    """
    try:
        gs = GlobalSearch(db)
        sqltext = f"select vercode from userformversion u where ufcode = '{fcode}'"
        res = gs.executeText(sqltext)
        result=[]
        for row in res.fetchall():
            result.append(row._asdict())
        return SimpleResult(status='T',value=result).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
# 分页获取下列表数据
@uffieldbp.route("/pagePullCombo/<tablename>/<int:pn>/<int:ps>/<valcol>")
def pagePullCombo(tablename:str,pn:int,ps:int,valcol:str):
    """
    分页获取下拉列表数据

    Args:
        tablename (str): 数据表名称
        pn (int): 页码
        ps (int): 页面尺寸
        lvcol (str): 标签列名称
        valcol (str): 取值列名称
    """
    try:
        pn=1 if pn<=0 else pn
        ps=30 if ps<=30 else ps
        cols = []
        cols.append(valcol)
        gs = GlobalSearch(db)
        sc = SearchCondition("jzflag",MatchLogic.from_Name("EQ"),"01")
        sl = SearchConditionList()
        sl.appendCondition(PartnerRS.from_name("AND"),sc)
    
        res= gs.pageTableInfo(pn,ps,tablename,cols,sl)
        return SimpleResult(status="T",value=res).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/makeFormRecordCode/<uftablename>")
def makeFormRecordCode(uftablename:str):
    """
    获取自定义表单记录编码

    Args:
        uftablename (str): 自定义表单记录表名称
    """
    try:
        tool = DBTool(db)
        import time
        timestr = time.strftime('%Y%m%d')
        newcode=tool.getNewCode(uftablename,'',timestr,6)
        return SimpleResult(status='T',value=newcode).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/saveFormRecord",methods=["POST"])
def saveFormRecord():
    """
    保存表单记录到数据库
    """
    try:
        params = request.json
        reds = params["records"]
        tblname = params["tblname"]
        #新增记录列表
        newlist = []
        #更新记录列表
        upgradelist=[]
        gs=GlobalSearch(db)
        for recinfo in reds:
            edtflag = recinfo["editflag"]
            rc = recinfo["record"]
            del rc["jztime"]
            if edtflag=="new":                
                del rc["ID"]
                import time
                current_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                rc["mktime"] = current_time
                newlist.append(rc)
            elif edtflag=="edit":
                upgradelist.append(recinfo["record"])
        # 添加新数据
        if(len(newlist)>0):
            gs.appentData(tblname,newlist)
        # 更新数据
        for rec in upgradelist:
            sl=SearchConditionList()
            cd = SearchCondition("ID",MatchLogic.from_Name("EQ"),rec["ID"])
            sl.appendCondition(PartnerRS.from_name("AND"),cd)
            del rec["ID"]
            gs.upgradeData(tblname,rec,sl)
        return SimpleResult(status="T").toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/deleteformrecord/<tblname>",methods=["POST"])
def deleteformrecord(tblname:str)->dict:
    """
    删除表单记录
    Keyword arguments:
    tblname 数据表名称
    Return: 简单结果模型
    """
    try:
        pms = request.json
        delrow = pms["delrows"]
        gs=GlobalSearch(db)
        sl = SearchConditionList()
        for row in delrow:
            sc=SearchCondition("ID",MatchLogic.from_Name("EQ"),row["ID"])
            sl.appendCondition(PartnerRS.from_name("OR"),sc)
        rcnt = gs.deleteData(tblname,sl)
        return SimpleResult(status="T",value=rcnt).toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()
    
@uffieldbp.route("/makeAccounts/<tblname>",methods=["POST"])   
def makeAccounts(tblname:str)->dict:
    """
    自定义表单记录记账
    Keyword arguments:
    argument tblname 记录表名称
    Return: 返回简单结果模型
    """
    
    try:
        pms=request.json
        rows = pms["rows"]
        gl=GlobalSearch(db)
        for rec in rows:
            sl = SearchConditionList()
            sc = SearchCondition("ID",MatchLogic.from_Name("EQ"),rec["ID"])
            sl.appendCondition(PartnerRS.from_name("AND"),sc)
            del rec["ID"]
            import time
            current_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            rec["jztime"] = current_time
            gl.upgradeData(tblname,rec,sl)
        return SimpleResult(status="T").toDict()
    except Exception as es:
        info = str(es)
        app.logger.error(info)
        return SimpleResult(msg=info).toDict()